﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Library;
namespace QuanLyThi
{
    public partial class FrmDangKyHocVien : Form
    {
        public static string mConnect = FrmMain.mConnect;
        private string msql = "";
        public DGC_Library mLibrary = new DGC_Library(mConnect);

        public FrmDangKyHocVien()
        {
            InitializeComponent();
            this.Load();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void LoadDataGrid()
        {
            if(!theolop.Checked)
                msql = "Select a.MS_HSSV,cast(a.Pass as char) as Pass ,a.Ngaydangky,a.hovaten,a.pass," +
                    " Case a.gioitinh  When 1 then N'Nam' Else N'Nữ' End as Gioitinh," +
                    " a.Namsinh,a.Diachi,a.Dienthoai,a.Mail from tb_hssv a " +
                    "left outer join tb_hssvlop b on a.ms_hssv=b.MS_hssv " +
                    "where b.ms_Lop is null order by a.hovaten";
            else
                msql = "Select a.MS_HSSV,cast(a.Pass as char) as Pass ,a.Ngaydangky,a.hovaten,a.pass," +
                  " Case a.gioitinh  When 1 then N'Nam' Else N'Nữ' End as Gioitinh," +
                  " a.Namsinh,a.Diachi,a.Dienthoai,a.Mail from tb_hssv a " +
                  "left outer join tb_hssvlop b on a.ms_hssv=b.MS_hssv " +
                  "where b.ms_Lop='" + lop.SelectedValue.ToString() + "' order by a.hovaten";
            
            DataTable dt = mLibrary.GetDataTable(msql, "table");
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = dt;
        }
       
        private void Load()
        {
            LoadDataGrid();
        }

        private void butThem_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable MaKT = mLibrary.GetDataTable("Select * from TB_hssv where MS_HSSV='"+mshocvien.Text+"'","a");

                if (MaKT.Rows.Count != 0)
                {
                    MessageBox.Show("Mã học viên đã tồn tại", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    if (mshocvien.Text != "" && hovaten.Text != "")
                    {
                        int a = 0;
                        if (gioitinh.Text == "Nam") a = 0;
                        else
                            a = -1;
                        msql = "Insert into TB_hssv (MS_HSSV,Pass,Ngaydangky,hovaten,gioitinh," +
                               " Namsinh,Diachi,Dienthoai,Mail,StampTime)" +
                               "  values ('" + mshocvien.Text + "'," +
                               " cast('"+matkhau.Text+"'as varbinary)," +
                               " convert(datetime,'"+ngaydangky.Value.ToString("MM/dd/yyyy")+"',103)," +
                               " N'"+hovaten.Text+"',"+a+"," +
                               " convert(datetime,'" + namsinh.Value.ToString("MM/dd/yyyy") + "',103)," +
                               " N'"+diachi.Text+"'," +
                               " N'"+dienthoai.Text+"'," +
                               " N'"+email.Text+"'," +
                               "  getdate())";
                        mLibrary.RunProc(msql);
                        if (theolop.Checked)
                        {
                            msql = "Insert into tb_hssvlop (ms_HSSV,MS_Lop,ngaydangky,stamptime) " +
                                   " values ('" + mshocvien.Text + "'," +
                                   " '" + lop.SelectedValue.ToString() + "', getdate(),getdate())";
                            mLibrary.RunProc(msql);
                        }
                        MessageBox.Show("Lưu thông tin thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                       
                    }
                    else
                    {
                        MessageBox.Show("Bạn vui lòng nhập đầy đủ thông tin", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }                
            }
            catch (Exception)
            {
                MessageBox.Show("Lưu thông tin không thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            //Load lai thong tin
            LoadDataGrid();
        }

        private void butXoa_Click(object sender, EventArgs e)
        {
            try
            {
                string Ma = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                if(Ma!="")
                {
                    DialogResult dialog = MessageBox.Show("Bạn có muốn xóa thông tin không?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dialog == DialogResult.Yes)
                    {
                        msql = "Delete from TB_hssv Where MS_HSSV='"+Ma+"'" ;
                        mLibrary.RunProc(msql);                        
                        //Load lai thong tin
                        this.LoadDataGrid();
                    }
                }
                else
                {
                    MessageBox.Show("Bạn vui lòng nhập đầy đủ thông tin", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Xóa thông tin không thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void butSua_Click(object sender, EventArgs e)
        {
            try
            {
                int a = 0;
                if (gioitinh.Text == "Nam") a = 0;
                else a = 1;
                msql = "Update TB_hssv set Pass=cast('"+matkhau.Text+"'as varbinary) ," +
                       " Ngaydangky=convert(datetime,'"+ngaydangky.Value.ToString("MM/dd/yyyy")+"',103) ," +
                       " hovaten=N'"+hovaten.Text+"' ," +
                       " gioitinh="+a+" ," +
                       " Namsinh=convert(datetime,'" + namsinh.Value.ToString("MM/dd/yyyy") + "',103) ," +
                       " Diachi=N'"+diachi.Text+"' ,Dienthoai=N'"+dienthoai.Text+"' ,Mail=N'"+email.Text+"' ," +
                       " StampTime=getdate() " +
                       " Where MS_HSSV='"+mshocvien.Text+"'";
                mLibrary.RunProc(msql);
                MessageBox.Show("Cập nhật thông tin thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                //Load lai thong tin
                this.LoadDataGrid();
               
            }
            catch (Exception)
            {
                MessageBox.Show("Cập nhật thông tin không thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }         
        }

      

        private void theolop_CheckedChanged(object sender, EventArgs e)
        {
            if (theolop.Checked)
            {
                msql = "Select MS_lop, tenlop from tb_Lopdaotao Order by Tenlop";
                DataTable dt = mLibrary.GetDataTable(msql, "table");
                lop.DataSource = dt;
                lop.DisplayMember = "tenlop";
                lop.ValueMember = "MS_lop";
                lop.Enabled = true;
            }
            else
            {
                lop.Enabled = false;
            }
        }

        private void lop_SelectionChangeCommitted(object sender, EventArgs e)
        {
            LoadDataGrid();
        }

        private void dataGridView1_DoubleClick(object sender, EventArgs e)
        {           
            mshocvien.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString().Trim();
            ngaydangky.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString().Trim();
            hovaten.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString().Trim();

            gioitinh.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString().Trim();
            namsinh.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString().Trim();
            diachi.Text = dataGridView1.CurrentRow.Cells[5].Value.ToString().Trim();
            dienthoai.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString().Trim();
            email.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString().Trim();
            matkhau.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString().Trim();
        }

        private void macdinhmatkhau_CheckedChanged(object sender, EventArgs e)
        {
            if (macdinhmatkhau.Checked)
                matkhau.Text = "123";
        }


    }
}
